package jp.co.dailyReport.weeklyReport.sql;

import java.util.Vector;
import java.util.Calendar;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import org.apache.log4j.Logger;
import jp.co.dailyReport.Executer;
import jp.co.dailyReport.weeklyReport.WeeklyReportWorkItem;

/**
 * 作業時間合計を取得するクラスです。
 */
public class WeeklyReportWorkItemTotalWorkingHoursExecuter implements Executer {

	private static Logger log = Logger.getLogger(WeeklyReportWorkItemTotalWorkingHoursExecuter.class);
	private int projectId;
	private Calendar workDateStart;
	private Calendar workDateEnd;

	public WeeklyReportWorkItemTotalWorkingHoursExecuter(int projectId,Calendar workDateStart,Calendar workDateEnd) {
		this.projectId = projectId;
		this.workDateStart = workDateStart;
		this.workDateEnd = workDateEnd;
	}

	/**
	 * 作業項目ID・作業日を基に、作業時間合計を取得します。
	 * 
	 * @param conn コネクションオブジェクト
	 * @return 作業時間合計
	 * @throws SQLException
	 */
	public Object[] execute(Connection conn) throws SQLException {

		ResultSet rset = null;
		PreparedStatement pstmt = null;
		String sql = "select sum(dw.workingHours) AS workingHours,dw.workItemId as workItemId " +
					 "from dailyReport AS d " +
					 "LEFT JOIN dailyReportWorkItem AS dw ON dw.dailyReportId = d.dailyReportId " + 
					 "LEFT JOIN workItem AS w ON dw.workItemId = w.workItemId " + 
					 "where w.projectId=? and d.workDate>=? and d.workDate<=? " +
					 "group by dw.workItemId";
		log.debug("sql: " + sql);
		Vector answer = new Vector();
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, projectId);
			pstmt.setDate(2, new Date(workDateStart.getTime().getTime()));
			pstmt.setDate(3, new Date(workDateEnd.getTime().getTime()));
			rset = pstmt.executeQuery();
			WeeklyReportWorkItem weeklyReportWorkItem;
			while(rset.next()) {
				weeklyReportWorkItem = new WeeklyReportWorkItem();
				weeklyReportWorkItem.setWorkItemId(rset.getInt("workItemId"));
				weeklyReportWorkItem.setWorkingHours(rset.getDouble("workingHours"));
				answer.add(weeklyReportWorkItem);
			}
		} finally {
			if(rset != null)
			 rset.close();
			if(pstmt != null)
			 pstmt.close();
		}
		return (WeeklyReportWorkItem[])answer.toArray(new WeeklyReportWorkItem[0]);
	}
}